package com.ecelin.tools;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author ecelin@ecelin.com
 */
public class DbService {
	public static void makeExcel() throws Exception {
		File file = new File(System.getProperty("user.dir"), "setting.properties");
		InputStream is;
		if (file.exists()) {
			is = new FileInputStream(file);
		} else {
			is = DbService.class.getClassLoader().getResourceAsStream("setting.properties");
		}
		Properties properties = new Properties();
		properties.load(is);
		is.close();
		String ip = properties.getProperty("mysql.ip");
		String username = properties.getProperty("mysql.username");
		String password = properties.getProperty("mysql.password");
		String dbs = properties.getProperty("mysql.databases");
		if (dbs == null || "".equals(dbs.trim())) {
			throw new Exception("数据库为必填项!");
		}
		StringJoiner sj = new StringJoiner(",", "", "");
		if (dbs.contains(",")) {
			String[] split = dbs.split(",");
			for (String db : split) {
				if (db == null || "".equals(db.trim())) {
					continue;
				}
				sj.add("'" + db.trim() + "'");
			}
		} else {
			sj.add("'" + dbs.trim() + "'");
		}

		Connection conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306", username, password);
		PreparedStatement stat = conn.prepareStatement(DataInfo.DATA_INFO_LIST.replace("?", sj.toString()));
		System.out.println("正在查询数据,请稍后......");
		ResultSet rs = stat.executeQuery();
		List<DataInfo> list = new ArrayList<>();
		int count = 1;
		String tbName = "";
		while (rs.next()) {
			String length = rs.getString("length");
			if (length.contains("(") && length.contains(")")) {
				length = length.substring(length.indexOf("(") + 1, length.indexOf(")"));
			} else {
				length = "";
			}
			DataInfo info = new DataInfo(count++
					, rs.getString("dbName")
					, rs.getString("tableName")
					, rs.getString("columnName")
					, rs.getString("columnDesc")
					, rs.getString("dataType")
					, length
					, rs.getString("priKey")
					, rs.getString("autoIncrement")
					, rs.getString("isNullable")
					, rs.getString("defaultValue")
					, rs.getString("tableDesc")
					, rs.getLong("tableRows")
					, rs.getLong("avgRowLength")
					, rs.getLong("dataLength")
			);
			String tableName = rs.getString("tableName");
			if (!tbName.equals(tableName)) {
			System.out.println("正在采集: "+rs.getString("dbName")+"."+rs.getString("tableName"));
				tbName = tableName;
			}
			list.add(info);
		}
		rs.close();
		stat.close();
		conn.close();

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("数据字典");
		HSSFRow headerRow = sheet.createRow(0);
		HSSFCellStyle headerCellStyle = workbook.createCellStyle();
		headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
		setBorder(headerCellStyle);
		HSSFFont headerRowFont = workbook.createFont();
		headerRowFont.setFontName("宋体");
		headerRowFont.setBold(true);
		headerCellStyle.setFont(headerRowFont);
		creatCell(headerRow, 0, "序号", headerCellStyle);
		creatCell(headerRow, 1, "数据库", headerCellStyle);
		creatCell(headerRow, 2, "表名", headerCellStyle);
		creatCell(headerRow, 3, "表描述", headerCellStyle);
		creatCell(headerRow, 4, "字段名", headerCellStyle);
		creatCell(headerRow, 5, "字段描述", headerCellStyle);
		creatCell(headerRow, 6, "字段类型", headerCellStyle);
		creatCell(headerRow, 7, "长度", headerCellStyle);
		creatCell(headerRow, 8, "主键", headerCellStyle);
		creatCell(headerRow, 9, "自增", headerCellStyle);
		creatCell(headerRow, 10, "允许空", headerCellStyle);
		creatCell(headerRow, 11, "默认值", headerCellStyle);
		creatCell(headerRow, 12, "表行数", headerCellStyle);
		creatCell(headerRow, 13, "行均大小", headerCellStyle);
		creatCell(headerRow, 14, "表数据大小", headerCellStyle);
		creatCell(headerRow, 15, "数据长度", headerCellStyle);
		sheet.createFreezePane(0, 1, 0, 1);
		sheet.setAutoFilter(new CellRangeAddress(0, 1, 0, 15));
		sheet.setColumnWidth(0, 7 * 256);
		sheet.setColumnWidth(1, 10 * 256);
		sheet.setColumnWidth(2, 35 * 256);
		sheet.setColumnWidth(3, 30 * 256);
		sheet.setColumnWidth(4, 35 * 256);
		sheet.setColumnWidth(5, 30 * 256);
		sheet.setColumnWidth(6, 10 * 256);
		sheet.setColumnWidth(7, 5 * 256);
		sheet.setColumnWidth(8, 7 * 256);
		sheet.setColumnWidth(9, 7 * 256);
		sheet.setColumnWidth(10, 10 * 256);
		sheet.setColumnWidth(11, 10 * 256);
		sheet.setColumnWidth(12, 15 * 256);
		sheet.setColumnWidth(13, 15 * 256);
		sheet.setColumnWidth(14, 15 * 256);
		sheet.setColumnWidth(7, 15 * 256);
		HSSFCellStyle normalCell = workbook.createCellStyle();
		normalCell.setAlignment(HorizontalAlignment.LEFT);
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		setBorder(normalCell);
		normalCell.setFont(font);
		HSSFCellStyle center = workbook.createCellStyle();
		center.setAlignment(HorizontalAlignment.CENTER);
		setBorder(center);
		center.setFont(font);
		for (DataInfo info : list) {
			HSSFRow temp = sheet.createRow(info.getIndex());
			creatCell(temp, 0, String.valueOf(info.getIndex()), center);
			creatCell(temp, 1, info.getDbName(), center);
			creatCell(temp, 2, info.getTableName(), normalCell);
			creatCell(temp, 3, info.getTableDesc(), normalCell);
			creatCell(temp, 4, info.getColumnName(), normalCell);
			creatCell(temp, 5, info.getColumnDesc(), normalCell);
			creatCell(temp, 6, info.getDataType(), center);
			creatCell(temp, 7, info.getLength(), center);
			creatCell(temp, 8, info.getPriKey(), center);
			creatCell(temp, 9, info.getAutoIncrement(), center);
			creatCell(temp, 10, info.getIsNullable(), center);
			creatCell(temp, 11, info.getDefaultValue(), center);
			creatCell(temp, 12, String.valueOf(info.getTableRows()), center);
			creatCell(temp, 13, String.valueOf(info.getAvgRowLength()), center);
			creatCell(temp, 14, String.valueOf(info.getDataLength()), center);
			creatCell(temp, 15, String.valueOf((info.getDataLength() + "").length()), center);
		}
		String name = new SimpleDateFormat("数据字典yyyyMMddHHmmss").format(new Date()) + ".xls";
		FileOutputStream out = new FileOutputStream(new File(FileSystemView.getFileSystemView().getHomeDirectory()
				, name));
		workbook.write(out);
		out.flush();
		out.close();
		workbook.close();
		System.out.println(name + "文件已经生成在桌面上!");
	}

	public static void setBorder(HSSFCellStyle style) {
		style.setBorderTop(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderBottom(BorderStyle.THIN);
	}

	public static void creatCell(HSSFRow row, int index, String description, HSSFCellStyle style) {
		HSSFCell cell = row.createCell(index);
		if (description == null) {
			cell.setCellValue("");
		} else {
			String trim = description.trim();
			cell.setCellValue(trim);
		}
		if (style != null) {
			cell.setCellStyle(style);
		}
	}
}


